package wineshop.dao.Impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import wineshop.dao.DaoFactory;
import wineshop.dao.HotelDao;
import wineshop.dao.RoomDao;
import wineshop.domain.Hotel;
import wineshop.domain.Room;
import wineshop.exception.DataAccessException;
import wineshop.service.RoomQueryHelper;
import wineshop.utils.ConvertVariant;
import wineshop.utils.DBUtils;

public class RoomDaoImpl implements RoomDao {
	
	private static final String ADD_ROOM = "insert into room(room_hotel,room_number,room_type,room_facility,room_status,room_desc) values(?,?,?,?,?,?)";
	private static final String GET_ALL_ROOM = "select * from room order by room_hotel";
	private static final String GET_ROOMBYID = "select * from room where room_id=?"; 
	private static final String DEL_ROOM="delete from room where room_id=?";
	private static final String UPDATE_ROOM = "update room set room_hotel=?,room_number=?,room_type=?,room_facility=?,room_status=?,room_desc=? where room_id=?";
	
	
  private String generateSQL(RoomQueryHelper helper) {
		
		String sql = "select * from room where 1=1 ";
		
		if (helper.getQryHotelName() != null)
			sql += " and room_hotel= " + helper.getQryHotelName();
		
		if (helper.getQryRoomType() != null)
			sql += " and room_type= '" + helper.getQryRoomType()+"'";
		
		if (helper.getQryRoomStatus() != null)
			sql += " and room_status= '" + helper.getQryRoomStatus()+"'";
		
		sql += " order by room_hotel";
		
		return sql;
		
	}



	public void AddRoom(Room room){
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		

		try {
			ps = ct.prepareStatement(ADD_ROOM);
            ps.setInt(1, room.getHotel().getHotelId());
            ps.setInt(2, room.getRoomNumber());
			ps.setString(3, room.getRoomType());
			ps.setString(4, ConvertVariant.ArrayToString(room.getRoomFacility()));
			ps.setString(5, room.getRoomStatus());
			ps.setString(6, room.getRoomDesc());
			
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("新增房间失败，请检查数据！");
		} finally {

			DBUtils.getInstance().releaseRes(ct, ps, null);
		}
		
		
	}

    public List<Room> GetAllRoom(){
    	Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Room> rooms = new ArrayList<Room>();
		try {
			ps = ct.prepareStatement(GET_ALL_ROOM);
			rs = ps.executeQuery();
			HotelDao hotelDao = (HotelDao) DaoFactory.GetDao("hotelDao");
			while (rs.next()) {
				Room room = new Room();
				Hotel hotel = new Hotel();
				hotel = hotelDao.GetHotelById(rs.getInt(2));
                room.setHotel(hotel);
                room.setRoomId(rs.getInt(1));
                room.setRoomNumber(rs.getInt(3));
                room.setRoomType(rs.getString(4));
                room.setFacility(rs.getString(5));
                room.setRoomFacility(ConvertVariant.StringToArray(rs.getString(5)));
                room.setRoomStatus(rs.getString(6));
                room.setRoomDesc(rs.getString(7));
				
				rooms.add(room);
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("房间列表获取失败!");
		} finally {

			DBUtils.getInstance().releaseRes(ct, ps, null);

		}
    	
    	
    	
    	return rooms;
    }

    public Room GetRoomById(int roomId) {
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Room room = null;
		
		try {
			ps = ct.prepareStatement(GET_ROOMBYID);
			ps.setInt(1, roomId);
			rs = ps.executeQuery();
			HotelDao hotelDao = (HotelDao) DaoFactory.GetDao("hotelDao");
			if (rs.next()) {
				room = new Room();
				Hotel hotel = new Hotel();
				hotel = hotelDao.GetHotelById(rs.getInt(2));
                room.setHotel(hotel);
                room.setRoomId(rs.getInt(1));
                room.setRoomNumber(rs.getInt(3));
                room.setRoomType(rs.getString(4));
                room.setFacility(rs.getString(5));
                room.setRoomFacility(ConvertVariant.StringToArray(rs.getString(5)));
                room.setRoomStatus(rs.getString(6));
                room.setRoomDesc(rs.getString(7));

			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

			DBUtils.getInstance().releaseRes(ct, ps, null);

		}

		return room;
	}

    public void UpdateRoom(Room room){
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;

		try {
			ps = ct.prepareStatement(UPDATE_ROOM);
			ps.setInt(1, room.getHotel().getHotelId());
            ps.setInt(2, room.getRoomNumber());
			ps.setString(3, room.getRoomType());
			ps.setString(4, ConvertVariant.ArrayToString(room.getRoomFacility()));
			ps.setString(5, room.getRoomStatus());
			ps.setString(6, room.getRoomDesc());
			ps.setInt(7, room.getRoomId());

			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("更新房间失败，请检查数据！");
		} finally {

			DBUtils.getInstance().releaseRes(ct, ps, null);
		}
		
		
	}
    
	public void delRoom(Integer roomId) {
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		

		try {
			ps = ct.prepareStatement(DEL_ROOM);
			ps.setInt(1, roomId);
			ps.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("房间信息删除失败，请检查数据！");
		} finally {

			DBUtils.getInstance().releaseRes(ct, ps, null);
		}
		
	}

	public List<Room> getRoomsByCondition(RoomQueryHelper helper) {
		String sql = this.generateSQL(helper);

		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Room room = null;
		List<Room> rooms = new ArrayList<Room>();

		try {
			ps = ct.prepareStatement(sql);
			rs = ps.executeQuery();
			HotelDao hotelDao = (HotelDao) DaoFactory.GetDao("hotelDao");
			while (rs.next()) {

				room = new Room();
				Hotel hotel = new Hotel();
				hotel = hotelDao.GetHotelById(rs.getInt(2));
                room.setHotel(hotel);
                room.setRoomId(rs.getInt(1));
                room.setRoomNumber(rs.getInt(3));
                room.setRoomType(rs.getString(4));
                room.setFacility(rs.getString(5));
                room.setRoomFacility(ConvertVariant.StringToArray(rs.getString(5)));
                room.setRoomStatus(rs.getString(6));
                room.setRoomDesc(rs.getString(7));
				
				rooms.add(room);				


			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("组合查询房间列表获取失败!");
		} finally {

			DBUtils.getInstance().releaseRes(ct, ps, rs);
		}

		return rooms;
	}

	public Long countRoomsByCondition(RoomQueryHelper helper) {
		String sql = this.generateSQL(helper);

		sql = sql.replace("*", "count(*) as total");
		sql = sql.substring(0, sql.indexOf("order by"));

		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Long count = 0L;

		try {
			ps = ct.prepareStatement(sql);
			rs = ps.executeQuery();
			if (rs.next()) {
				count = rs.getLong("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtils.getInstance().releaseRes(ct, ps, rs);
		}

		return count;
	}

	public List<Room> getScopedRooms(RoomQueryHelper helper, int startIndex,
			int fetchSize) {

		String sql = this.generateSQL(helper);
		sql += " limit " + startIndex + "," + fetchSize;

		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Room room = null;
		List<Room> rooms = new ArrayList<Room>();

		try {
			ps = ct.prepareStatement(sql);
			rs = ps.executeQuery();
			HotelDao hotelDao = (HotelDao) DaoFactory.GetDao("hotelDao");
			while (rs.next()) {

				room = new Room();
				Hotel hotel = new Hotel();
				hotel = hotelDao.GetHotelById(rs.getInt(2));
                room.setHotel(hotel);
                room.setRoomId(rs.getInt(1));
                room.setRoomNumber(rs.getInt(3));
                room.setRoomType(rs.getString(4));
                room.setFacility(rs.getString(5));
                room.setRoomFacility(ConvertVariant.StringToArray(rs.getString(5)));
                room.setRoomStatus(rs.getString(6));
                room.setRoomDesc(rs.getString(7));
				
				rooms.add(room);	

			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("组合查询房间列表获取失败!");
		} finally {
			DBUtils.getInstance().releaseRes(ct, ps, rs);
		}

		return rooms;

	}




}
